package com.example.coursaccino;


import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class UserDAO {
	
	private final String TEXT_TYPE = " TEXT";
	private final String INTEGER_TYPE = " INTEGER";
	private final String COMMA_SEP = ",";
	
	private final String SQL_CREATE_USER_TABLE =
	    "CREATE TABLE " + DatabaseContract.UserEntry.TABLE_NAME + " (" +
		DatabaseContract.UserEntry._ID + " INTEGER PRIMARY KEY " +
		"AUTOINCREMENT," +
	    DatabaseContract.UserEntry.COLUMN_NAME_USERNAME + TEXT_TYPE + 
	    COMMA_SEP +
	    DatabaseContract.UserEntry.COLUMN_NAME_PASSWORD + TEXT_TYPE +
	    COMMA_SEP +
	    DatabaseContract.UserEntry.COLUMN_NAME_DESIGNATION + INTEGER_TYPE +
	    " )";

	private final String SQL_DELETE_USER_TABLE =
	    "DROP TABLE IF EXISTS " + DatabaseContract.UserEntry.TABLE_NAME;
	
	
	public UserDAO () {
		
	}
	
	public String getCreateTablesSQLQuery() {
		return SQL_CREATE_USER_TABLE;
	}
	
	public String getDeleteTablesSQLQuery() {
		return SQL_DELETE_USER_TABLE;
	}

	public boolean validateUser (String username, String password) {
		SQLiteDatabase db = WelcomeActivity.dbHelper.getReadableDatabase();
		Cursor c;
		c = query(username, db);
		if (c.moveToFirst()) {
			String pwd = c.getString(c.getColumnIndex("password"));
			if (pwd.equals(password)) {
				db.close();
				return true;
			}
		}
		db.close();
		return false;
	}
	
	public boolean query_and_insert_user (User usr) {
		SQLiteDatabase db = WelcomeActivity.dbHelper.getWritableDatabase();
		boolean ret = false;
		db.beginTransaction();
		try {
			Cursor c;
			c = query(usr.getUsername(), db);
			
			if (c.moveToFirst()) {
				System.out.println("Item found");
				ret = false;
			} else {
				System.out.println("Item NOT found");
				insert(db, usr);
				ret = true;
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			// Error in between database transaction
			e.printStackTrace();
			ret = false;
		} finally {
			db.endTransaction();
			db.close();
		}
		
		return ret;
	}
	
	private Cursor query (String username, SQLiteDatabase db) {
		// SQLiteDatabase db = WelcomeActivity.dbHelper.getReadableDatabase();
		// Define a projection that specifies which columns from the database
		// you will actually use after this query.
		String[] projection = {
		    DatabaseContract.UserEntry._ID,
		    DatabaseContract.UserEntry.COLUMN_NAME_USERNAME,
		    DatabaseContract.UserEntry.COLUMN_NAME_PASSWORD,
		    DatabaseContract.UserEntry.COLUMN_NAME_DESIGNATION
		    };

		Cursor c = db.query(
			DatabaseContract.UserEntry.TABLE_NAME,  // The table to query
			projection,                             // The columns to return
			DatabaseContract.UserEntry.COLUMN_NAME_USERNAME + 
			"=?",
			new String[] { username },
		    null,                           // don't group the rows
		    null,                           // don't filter by row groups
		    null                           // The sort order
		    );
		
//		if (c != null) {
//            c.moveToFirst();
//        }
		
//		if  (c.moveToFirst()) {
//            do {
//            	String user = c.getString(c.getColumnIndex("username"));
//            	System.out.println(user);
//            }while (c.moveToNext());
//        }
		
		System.out.println("Item found from query " + c);
		
		return c;
	}
	
	private void insert (SQLiteDatabase db, User usr) {
		// Gets the data repository in write mode
		// SQLiteDatabase db = WelcomeActivity.dbHelper.getWritableDatabase();

		// Create a new map of values, where column names are the keys
		ContentValues values = new ContentValues();
		values.put(DatabaseContract.UserEntry.COLUMN_NAME_USERNAME, usr.getUsername());
		values.put(DatabaseContract.UserEntry.COLUMN_NAME_PASSWORD, usr.getPassword());
		values.put(DatabaseContract.UserEntry.COLUMN_NAME_DESIGNATION, usr.getDesignation());
				
		// Insert the new row, returning the primary key value of the new row
		db.insert(DatabaseContract.UserEntry.TABLE_NAME, 
							 null, 
							 values);
		
		// System.out.println("Item inserted into DB " + username + " " + password + " " + desg);
	}
	
	private void remove (String username) {
		SQLiteDatabase db = WelcomeActivity.dbHelper.getWritableDatabase();
		db.delete(DatabaseContract.UserEntry.TABLE_NAME, 
				// The values for the WHERE clause
				DatabaseContract.UserEntry.COLUMN_NAME_USERNAME + 
				"=?",
				new String[] { username }
		);
	}
}
